﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class BunkerInventorySQL
    {
        public static string GetAllBunkerInventoryBODandBOR()
        {
            return @"            
                    Select distinct
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre_fullname,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vVMVoyageSrch.Vessel vessel_code,
                    vVMVoyageSrch.VesselName headfixture_name,
                    vVMVoyageBunkerSummarySrch.AvgPrice price,
                    vVMVoyageBunkerSummarySrch.Quality quality,
                    vVMVoyageBunkerSummarySrch.Quantity quantity,
                    vVMVoyageBunkerSummarySrch.Description description,
                    vVMVoyageBunkerSummarySrch.DisplayOrder order_,
                    vVMVoyageSrch.Type type,
                    vVMVoyageSrch.LastUpdatedBy last_updated_by,
                    vVMVoyageSrch.LastUpdatedDate last_updated_date,
                    vVMVoyageSrch.OperatedBy operated_by,
                     vVMVoyageSrch.Status VoyageStatus  ,
                    vVMVoyageSrch.EndVoyageDate end_voyage_date             
                     FROM VVMVOYAGESRCH JOIN VPROFITCENTREORGANISATIONSRCH
                      ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID 
                      JOIN VVMVOYAGEBUNKERSUMMARYSRCH ON VVMVOYAGESRCH.ID = VVMVOYAGEBUNKERSUMMARYSRCH.VOYAGE   
                    LEFT OUTER JOIN VMVoyageBunkerSummary on VMVoyageBunkerSummary.ID = VVMVOYAGEBUNKERSUMMARYSRCH.ID
                      WHERE vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     (vVMVoyageBunkerSummarySrch.AvgPrice <> 0) AND 
                     (vVMVoyageBunkerSummarySrch.Quantity <> 0) AND 
                     (vVMVoyageBunkerSummarySrch.Description = 'Bunkers on delivery to TC' 
                     OR vVMVoyageBunkerSummarySrch.Description = 'bunkers on redelivery from TC') 
                    and (VMVoyageBunkerSummary.Note is null or VMVoyageBunkerSummary.Note = '')
                    and vVMVoyageSrch.LastUpdatedDate > '2013-07-23 00:00'
                    AND vVMVoyageSrch.Status in ('Voyage Commenced','Voyage Completed')
                    --AND vVMVoyageSrch.Status <> 'Cancelled'
            ";
        }

        public static string GetAllBunkerInventoryTotalConsumption()
        {
            return @"            
                    Select distinct
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre_fullname,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vVMVoyageSrch.VesselName headfixture_name,
                    vVMVoyageSrch.Vessel vessel_code,
                    vVMVoyageBunkerSummarySrch.AvgPrice price,
                    vVMVoyageBunkerSummarySrch.Quality quality,
                    vVMVoyageBunkerSummarySrch.Quantity quantity,
                    vVMVoyageBunkerSummarySrch.Description description,
                    vVMVoyageBunkerSummarySrch.DisplayOrder order_,
                    vVMVoyageSrch.Type type,
                    vVMVoyageSrch.LastUpdatedBy last_updated_by,
                    vVMVoyageSrch.LastUpdatedDate last_updated_date,
                    vVMVoyageSrch.OperatedBy operated_by,
                     vVMVoyageSrch.Status VoyageStatus,
                    vVMVoyageSrch.EndVoyageDate end_voyage_date               
                     FROM VVMVOYAGESRCH JOIN VPROFITCENTREORGANISATIONSRCH
                      ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID 
                      JOIN VVMVOYAGEBUNKERSUMMARYSRCH ON VVMVOYAGESRCH.ID = VVMVOYAGEBUNKERSUMMARYSRCH.VOYAGE   
                    LEFT OUTER JOIN VMVoyageBunkerSummary on VMVoyageBunkerSummary.ID = VVMVOYAGEBUNKERSUMMARYSRCH.ID
                      WHERE vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     (vVMVoyageBunkerSummarySrch.AvgPrice <> 0) AND 
                     (vVMVoyageBunkerSummarySrch.Quantity <> 0) AND 
                    vVMVoyageBunkerSummarySrch.Description = 'Total consumption'
                    and (VMVoyageBunkerSummary.Note is null or VMVoyageBunkerSummary.Note = '')
                    and vVMVoyageSrch.LastUpdatedDate > '2013-07-23 00:00'
                    and CAST(vVMVoyageSrch.EndVoyageDate AS Date)  = @endDate
                    --AND vVMVoyageSrch.Status = 'Voyage Commenced'
                    AND vVMVoyageSrch.Status = 'Voyage Completed'                    
            ";
        }

        public static string GetAllBunkerInventoryMigration()
        {
            return @"            
                      Select distinct
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre_fullname,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vVMVoyageSrch.VesselName headfixture_name,
                    vVMVoyageSrch.Vessel vessel_code,
                    vVMVoyageBunkerSummarySrch.AvgPrice price,
                    vVMVoyageBunkerSummarySrch.Quality quality,
                    vVMVoyageBunkerSummarySrch.Quantity quantity,
                    vVMVoyageBunkerSummarySrch.Description description,
                    vVMVoyageBunkerSummarySrch.DisplayOrder order_,
                    vVMVoyageSrch.Type type,
                    vVMVoyageSrch.LastUpdatedBy last_updated_by,
                    vVMVoyageSrch.LastUpdatedDate last_updated_date,
                    vVMVoyageSrch.OperatedBy operated_by,
                     vVMVoyageSrch.Status VoyageStatus,
                    vVMVoyageSrch.EndVoyageDate end_voyage_date    
                     FROM VVMVOYAGESRCH JOIN VPROFITCENTREORGANISATIONSRCH
                      ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID 
                      JOIN VVMVOYAGEBUNKERSUMMARYSRCH ON VVMVOYAGESRCH.ID = VVMVOYAGEBUNKERSUMMARYSRCH.VOYAGE   
                    LEFT OUTER JOIN VMVoyageBunkerSummary on VMVoyageBunkerSummary.ID = VVMVOYAGEBUNKERSUMMARYSRCH.ID
                      WHERE vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     (vVMVoyageBunkerSummarySrch.AvgPrice <> 0) AND 
                     (vVMVoyageBunkerSummarySrch.Quantity <> 0) AND 
                     (vVMVoyageBunkerSummarySrch.Description = 'Bunkers on delivery to TC' 
                     OR vVMVoyageBunkerSummarySrch.Description = 'bunkers on redelivery from TC' 
                     OR vVMVoyageBunkerSummarySrch.Description = 'Total consumption') 
                    and (VMVoyageBunkerSummary.Note is null or VMVoyageBunkerSummary.Note = '')
                    and vVMVoyageSrch.VoyageNo = @VoyageNo
                    and vVMVoyageSrch.VesselName = @VesselName
                    and vVMVoyageSrch.LastUpdatedDate > '2013-07-23 00:00'
                    and vVMVoyageBunkerSummarySrch.Description = @description
            ";
        }

        public static string CountBunkerInvTemp()
        {
//            return @"
//            select count(*) from i_BunkerInventory where
//            profit_centre_fullname = @profit_centre_fullname
//           and voyage_no = @voyage_no
//           and headfixture_name = @headfixture_name
//           and price = @price
//           and quality = @quality
//           and quantity = @quantity
//           and description = @description
//           and order_ = @order_
//           and type = @type
//           and last_updated_by = @last_updated_by
//           and last_updated_date = @last_updated_date
//           and operated_by = @operated_by
//            ";

            return @"
            select count(*) from i_BunkerInventory where
            profit_centre_fullname = @profit_centre_fullname
           and voyage_no = @voyage_no
           and headfixture_name = @headfixture_name
           and quality = @quality
           and description = @description
           and type = @type
            ";
        }

        public static string UpdateBunkerInvVoyageStatus()
        {
            return @"update i_BunkerInventory set VoyageStatus = @VoyageStatus where
            profit_centre_fullname = @profit_centre_fullname
           and voyage_no = @voyage_no
           and headfixture_name = @headfixture_name
           and quality = @quality
           and description = @description
           and type = @type";
        }

        public static string InsertBunkerInvTemp()
        {
            return @"
            INSERT INTO i_BunkerInventory
           ([profit_centre_fullname]
           ,[voyage_no]
           ,[headfixture_name]
           ,[price]
           ,[quality]
           ,[quantity]
           ,[description]
           ,[order_]
           ,[type]
           ,[last_updated_by]
           ,[last_updated_date]
           ,[pickup_flag]
           ,[insert_date]
            ,[operated_by]
            ,[VoyageStatus]
            ,[vessel_code]
            ,[end_voyage_date]
            )
     VALUES
           (@profit_centre_fullname
           ,@voyage_no
           ,@headfixture_name
           ,@price
           ,@quality
           ,@quantity
           ,@description
           ,@order_
           ,@type
           ,@last_updated_by
           ,@last_updated_date
           ,@pickup_flag
           ,@insert_date
            ,@operated_by
            ,@VoyageStatus
            ,@vessel_code
            ,@end_voyage_date
           )
            ";
        }

        public static string GetPendingAllBunkerInvIW()
        {
//            return @"select * from i_BunkerInventory where voyage_no in (8,9,10,11,12,13,14,15) and
//headfixture_name like '%mutiara%' 
//and description =  'Total consumption'
//order by voyage_no";
//            //return "select * from i_BunkerInventory";

           // return "select * from i_BunkerInventory where pickup_flag = '0'"; //order by IW the IR

          //  return "select * from i_BunkerInventory where BunkerInventory_id in (11025,11026)"; //order by IW the IR
            //select * from i_BunkerInventory where pickup_flag = '0' and description = 'Bunkers on Redelivery from TC'  UNION
            return @"select * from i_BunkerInventory where pickup_flag = '0' and description = 'Total consumption' and last_updated_date > '2013-06-01'
                    UNION
                    select * from i_BunkerInventory where pickup_flag = '0' and description = 'Bunkers on Delivery to TC' and last_updated_date > '2013-06-01'";
        }
        public static string GetPendingAllBunkerInvTotalConsumption()
        {
            return @"select * from i_BunkerInventory where pickup_flag = '0' and description = 'Total consumption'";
        }

        public static string GetPendingAllBunkerInvBunkerOnDelivery()
        {
            return @"select * from i_BunkerInventory where pickup_flag = '0' and description = 'Bunkers on Delivery to TC'";
        }

        public static string GetPendingAllBunkerInvBunkerOnReDelivery()
        {
            return @"select * from i_BunkerInventory where pickup_flag = '0' and description ='Bunkers on Redelivery from TC'";
        }

        public static string UpdateBunkerInvPickup_flagStatus()
        {
            return @"update i_BunkerInventory set 
            pickup_flag=@pickup_flag,
            pickup_date=@pickup_date,
            remark=@remark 
            where BunkerInventory_id=@BunkerInventory_id ";
        }

        public static string GetAllPendingBunkerInventory(string p)
        {
            return string.Format(@"select distinct voyage_no, headfixture_name, profit_centre_fullname, description, convert(char, pickup_date, 101)pickup_date , remark from i_BunkerInventory 
            where pickup_flag = 0 
            and convert(char,voyage_no)+headfixture_name+ profit_centre_fullname+replace(convert(char(11), pickup_date, 106),' ','-')+ remark like '%{0}%'
            order by voyage_no", p);
        }

        public static string GetAllPendingBunkerInventory()
        {
            return @"select distinct voyage_no, headfixture_name, profit_centre_fullname, description , convert(char, pickup_date, 101)pickup_date , remark from i_BunkerInventory where pickup_flag = 0 order by voyage_no";
        }

        public static string GetAllPendingCloseBunkerInventory()
        {
            return @"select distinct voyage_no, headfixture_name, profit_centre_fullname, convert(char, pickup_date, 101)pickup_date , remark from i_BunkerInventory order by voyage_no";
        }

        public static string GetAllPendingBunkerInventoryCount()
        {
            return @"select count(*) from i_BunkerInventory where pickup_flag = 0";
        }

        public static string GetAllImportedBunkerInventory()
        {
            return "select  distinct profit_centre_fullname,voyage_no,headfixture_name,quality,description,type  from i_BunkerInventory where pickup_flag in (0,1,2)";
        }
    }
}
